<?php
namespace App\Admin\Http\Controllers\Web;

use App\Admin\Eloquent\Code;
use App\Admin\Eloquent\Project;
use App\Admin\Eloquent\ProjectProfit;
use App\Admin\Eloquent\Role;
use App\Admin\Eloquent\User;
use App\Admin\Eloquent\UserInfo;
use App\Admin\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Extensions\Pagination\BootstrapThreePresenter;
use Illuminate\Pagination\LengthAwarePaginator;
use DB;

class CooController extends Controller
{
    /*
     * 销售统计
     */
    public function index(Request $request){

        $query=$request->only('org_code','dept_code','start_at','end_at','time');

        $A='';$B='';$C='';$D='';
        empty($query['start_at']) or $A="AND user_info.created_at >="."'".$query['start_at']."'";
        empty($query['end_at']) or $B="AND user_info.created_at <="."'".$query['end_at']."'";
        empty($query['org_code']) or $C="AND org_code ="."'".$query['org_code']."'";
        $dept=null;
        if(!empty($query['org_code'])){
            $C="AND org_code ="."'".$query['org_code']."'";
            $dept=Code::where('parent_code','=',$query['org_code'])->get();
        }
        empty($query['dept_code']) or $D="AND org_code ="."'".$query['dept_code']."'";
        if(empty($query['time'])){
            $query['time']=date('Y-m-d H:i:s');
        }

        $end_list=DB::select("SELECT a.name,b.invite_num,c.valid_num,d.invite_money,e.repayment_money,f.wait_payment_money,h.is_payment_money FROM
(SELECT broker_code ,`name` FROM `user_info` WHERE `invite_code`=`broker_code` ".$C." ".$D.") a LEFT JOIN
(SELECT COUNT(broker_code) AS 'invite_num', broker_code FROM `user_info` WHERE 1=1 ".$A." ".$B." GROUP BY broker_code) b ON a.broker_code=b.broker_code LEFT JOIN
(SELECT COUNT(g.user_id)  AS 'valid_num', g.`broker_code` FROM (select `project_profits`.* from `project_profits` LEFT JOIN `user_info` ON `user_info`.`user_id`=`project_profits`.`user_id` WHERE `project_profits`.`type`=1 ".$A." ".$B." GROUP BY `project_profits`.user_id ) g GROUP BY g.`broker_code`) c ON a.broker_code=c.broker_code LEFT JOIN
(SELECT `project_profits`.broker_code ,SUM(money) AS invite_money,STATUS FROM `project_profits` LEFT JOIN `user_info` ON `user_info`.`user_id`=`project_profits`.`user_id` WHERE `project_profits`.`type`=1 ".$A." ".$B." GROUP BY `project_profits`.broker_code) d ON a.broker_code=d.broker_code LEFT JOIN
(SELECT SUM(money) AS repayment_money,broker_code FROM `project_profits` WHERE `type`=1  and ( ( `input_time` > '".$query['start_at']."' and `input_time` < '".$query['end_at']."' and `pay_time` is null ) OR (`pay_time` > '".$query['start_at']."' and `pay_time` < '".$query['end_at']."') ) GROUP BY broker_code) e  ON a.broker_code=e.broker_code LEFT JOIN
(SELECT SUM(money) AS wait_payment_money,broker_code FROM `project_profits` left join `projects`ON `projects`.`id`=`project_profits`.`project_id` WHERE `project_profits`.type=1 AND `project_profits`.status=0 AND `projects`.interest_at < '".$query['time']."' GROUP BY project_profits.broker_code) f  ON a.broker_code=f.broker_code  LEFT JOIN
(SELECT SUM(money) AS is_payment_money,broker_code FROM `project_profits` left join `projects`ON `projects`.`id`=`project_profits`.`project_id` WHERE `project_profits`.type=1 AND `project_profits`.status=1 AND (`project_profits`.pay_time > '".$query['time']."' and `project_profits`.pay_time < '".date('Y-m-d H:i:s')."' )GROUP BY project_profits.broker_code) h ON a.broker_code=h.broker_code
");


        $page=$request->page;
        if(empty($page)){
            $page=1;
        }
        $start=($page-1)*15;
        $r_end_list=collect($end_list)->sortByDesc('invite_num')->slice($start,15);
        $r_lists=new LengthAwarePaginator($r_end_list,count($end_list),15);
        $r_lists->setPath('/admin/web/coo/sale');
        if (!empty($query)) {
            $r_lists->appends($query);
        }
        $links = (new BootstrapThreePresenter($r_lists))->render();
        $lists=$r_lists->items();
        //机构
        $org=Code::whereNull('parent_code')->where('type_id','=','DEPARTMENT')->get();
        return view('admin.web.coo.sale_index',compact('lists','query','links','org','dept'));
    }
    /*
     *异步请求部门编码
     */
    public function getDeptCode(Request $request ,$code){
        $dept_code=Code::where('parent_code',$code)->get();
        $dept_code=collect($dept_code)->groupBy('parent_code');
        return $dept_code;
    }
    /*
     *运营统计
     */
    public function cooStatistic(Request $request){

        $query=$request->only('start_at','end_at');

        $project=Project::query();
        if(!empty($query['start_at'])){
            $project->where('published_at','>=',$query['start_at']);
        }
        if(!empty($query['end_at'])){
            $project->where('published_at','<=',$query['end_at']);
        }
        //项目数
        $project_num=$project->count();

        //期间交易量
        $records=ProjectProfit::query();
        if(!empty($query['start_at'])){
            $records->where('input_time','>=',$query['start_at']);
        }
        if(!empty($query['end_at'])){
            $records->where('input_time','<=',$query['end_at']);
        }
        $records=$records->where('type','1');
        $record_trades= $records->sum('money');

        //期间注册人数
        $register_user=UserInfo::query();
        if(!empty($query['start_at'])){
            $register_user->where('created_at','>=',$query['start_at']);
        }
        if(!empty($query['end_at'])){
            $register_user->where('created_at','<=',$query['end_at']);
        }
        $register_user=$register_user->count();

        //期间有效投资人数
        $records_num=$records->groupBy('user_id')->count();

        //为投资人赚取的收益
        $records_money=$records->sum('profit');

        $term=array();
        $rate=null;
        foreach($project->get() as $item){
            $arr=array();
            $item->auto_rate='9%=2%';
            if(preg_match_all('/\d+/',$item->auto_rate,$arr)){
                for($i = 0; $i <count($arr['0']); $i++){
                    $rate=$rate+$arr['0'][$i];
                }
            }
            switch($item->finance_time_cate){
                case 'd':
                    $num=$item->finance_time_num;
                    break;
                case 'm':
                    $num=$item->finance_time_num*30;
                    break;
                case 'mm':
                    $num=$item->finance_time_num*30;
                    break;
                case 'y':
                    $num=$item->finance_time_num*360;
                    break;
            }
           $term[]=$num;
        }
        $project_term=collect($term)->sum();
        $project_rate=$rate;
        //平均预期年化收益率
        $ms_rate=number_format($project_rate/$project_num,2);//(100%)
        //平均项目期限
        $ms_term=number_format($project_term/$project_num,2);//(天)


        $record=$records->groupBy('status')->get();

        $expire_money='0.00';
        $record_profit='0.00';
        $record_money='0.00';
        if(!empty($record['1'])){
            //到期量
            $expire_money=collect($record['1'])->sum('money');
        }
        if(!empty($record['0'])){
            //待收收益
            $record_profit=collect($record['0'])->sum('profit');
            //时点存量
            $record_money=collect($record['0'])->sum('money');
        }


        $list['project_num']=$project_num;
        $list['register_user']=$register_user;
        $list['record_trades']=$record_trades;
        $list['records_num']=$records_num;
        $list['records_money']=$records_money;
        $list['ms_rate']=$ms_rate;
        $list['ms_term']=$ms_term;
        $list['expire_money']=$expire_money;
        $list['record_profit']=$record_profit;
        $list['record_money']=$record_money;
        return view('admin.web.coo.coo_index',compact('list','query'));
    }


    /**
     * 用户投资情况明细查询
     * 
     * @param  Request $request [description]
     * @return [type]           [description]
     */
    public function userInvestInfo(Request $request,string $download = ''){
        $args = $request->only('start_time', 'end_time');

        $query = DB::table('projects_user as a')
                ->leftJoin('projects as b', 'a.projects_id', '=', 'b.id')
                ->leftJoin('user_info as c', 'a.user_id', '=', 'c.user_id')
                ->leftJoin('user_info as d', 'a.broker_code', '=', 'd.invite_code')
                ->leftJoin('organizational_users as e', 'd.org_code' ,'=', 'e.code')
                ->orderBy('a.invest_time', 'desc')
                ->orderBy('c.name','desc')
                ->select(DB::raw('e.company_name,
                                    d.user_id as broker_id,
                                    d.name as broker_name,
                                    c.user_id,
                                    c.name,
                                    b.projects_name,
                                    a.invest_money,
                                    b.finance_time_num,
                                    b.finance_time_cate,
                                    b.auto_rate,
                                    a.invest_time,
                                    b.interest_at,
                                    b.trade_repayment_date,
                                    b.status'));

        //追加查询条件
        if(!empty($args['start_time']))
            $qurey = $query->where('a.invest_time','>=',$args['start_time']);

        if(!empty($args['end_time']))
            $qurey = $query->where('a.invest_time','<=',$args['end_time']);

        if($download == 'download'){
            $data = $query->get();
            qeury_export_excel('用户详情',$data);
        }

        $data = $query->paginate(15);
        // dd($data);
        $links = $data->render();
        return view('admin.business.invest.index',compact('data','links','args'));
    }

    /**
     * 用户投资统计
     * 
     * @return [type] [description]
     */
    public function userInvestCount(string $download = ''){
        $query = DB::table('user_info as a')
                ->leftJoin('users as b', 'a.user_id', '=', 'b.id')
                ->leftJoin('user_info as c', 'a.broker_code', '=', 'c.invite_code')
                ->leftJoin('organizational_users as d', 'c.org_code', '=', 'd.code')
                ->leftJoin(DB::raw('(select user_id,count(id) as invest_count,sum(invest_money) as invest_money_count from projects_user group by user_id) as e'),'a.user_id', '=', 'e.user_id')
                ->select(DB::raw('
                        d.company_name,
                        c.user_id as broker_id,
                        c.name as broker_name,
                        a.user_id,
                        a.name,
                        b.phone_number,
                        a.id_card,
                        b.created_at,
                        b.email,
                        ifnull(e.invest_money_count,0) as invest_money_count,
                        ifnull(e.invest_count,0) as invest_count, 
                        b.last_login_at'))
                ->orderBy('b.created_at','desc');

        if($download == 'download'){
            $data = $query->get();
            qeury_export_excel('用户详情',$data);
        }


        $data = $query->paginate(15);

        $links = $data->render();

        return view('admin.business.invest.invest_count',compact('data','links'));
    }
}